package com.ld.shieldsb.dao;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import com.ld.shieldsb.annotation.util.AnnotationUtil;
import com.ld.shieldsb.common.core.reflect.FunctionUtil;
import com.ld.shieldsb.common.core.reflect.FunctionUtil.Property;
import com.ld.shieldsb.dao.model.JoinModel;
import com.ld.shieldsb.dao.model.PageNavigationBean;
import com.ld.shieldsb.dao.model.QueryModel;
import com.ld.shieldsb.dao.model.SqlModel;
import com.ld.shieldsb.dao.util.LogUtil;

import lombok.extern.slf4j.Slf4j;

@Slf4j
public class MySqlBaseDao extends TransactionImplBaseDao {
    private static final Pattern CONDITION_PATTERN = Pattern.compile("^(?i)select\\s+(.+?)\\s+from"); // ^表示开头非中间 (?i)忽略大小写，将查询字段替换为select
                                                                                                      // count(1) from

    public MySqlBaseDao() {
        this(DataKeyStatic.CREDIT_MYSQL_DATA_SOURE_KEY);
    }

    public MySqlBaseDao(String key) {
        super(key);
    }

    /**
     * 
     * 数据库是否存在某给字段值
     * 
     * @Title exists
     * @param key
     *            数据库字段
     * @param value
     *            字段值
     * @return boolean
     */
    @Override
    public <T> boolean exists(Class<T> classOfT, String key, Object value) {
        String tableName = getTableName(classOfT);
        return getCount("SELECT 1 FROM " + tableName + " WHERE " + key + "=? LIMIT 1", value) > 0;
    }

    /**
     * 
     * 根据UUID获取数据
     * 
     * @Title findByUuid
     * @param uuid
     *            uuid
     * @return T
     */
    public <T> T findByUuid(Class<T> classOfT, String uuid) {
        String tableName = getTableName(classOfT);
        return getOne(classOfT, "SELECT * FROM " + tableName + " WHERE uuid=? LIMIT 1", uuid);
    }

    /**
     * 
     * 获取某个表，某个字段的最大值
     * 
     * @Title getMax
     * @param table
     *            表名
     * @param col
     *            字段名
     * @return long
     */
    public <T> long getMax(Class<T> classOfT, String table, String col) {
        return getLong("SELECT MAX(" + col + ") FROM " + table);
    }

    /**
     * 
     * 获取默认表的某个字段的最大值
     * 
     * @Title getMax
     * @param columnName
     *            字段
     * @return long
     */
    public <T> long getMax(Class<T> classOfT, String columnName) {
        String tableName = getTableName(classOfT);
        String sql = "SELECT MAX(" + columnName + ") FROM " + tableName;
        return getLong(sql);
    }

    /**
     * 
     * 根据主键id获取数据
     * 
     * @Title findById
     * @param id
     *            主键id
     * @return T
     */
    @Override
    public <T> T findById(Class<T> classOfT, Object id) {
        return findById(classOfT, "id", id);
    }

    /**
     * 
     * 根据某个字段获取数据
     * 
     * @Title findById
     * @param key
     *            字段
     * @param value
     *            字段值
     * @return T
     */
    @Override
    public <T> T findById(Class<T> classOfT, String key, Object value) {
        String tableName = getTableName(classOfT);
        return getOne(classOfT, "SELECT * FROM " + tableName + " WHERE " + key + "=? LIMIT 1", value);
    }

    /**
     * 
     * 根据查询条件返回map
     * 
     * @Title getMap
     * @param queryModel
     *            查询条件
     * @return Map<String,Object>
     */
    @Override
    public <T> Map<String, Object> getMap(Class<T> classOfT, QueryModel queryModel) {
        String tableName = getTableName(classOfT);
        String sql = "select " + queryModel.getSelectFields() + " from " + tableName + " " + queryModel.getNoOrderQueryStr();
        return getMap(sql, queryModel.getParams());
    }

    private Object updateAndReturnId(String sql, Object... params) {
        if (canSave) {
            Connection connection = null;
            PreparedStatement ps = null;
            ResultSet rs = null;
            try {
                connection = getCon();
                ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                int i = 1;
                for (Object o : params) {
                    ps.setObject(i, o);
                    i++;
                }
                ps.executeUpdate();
                rs = ps.getGeneratedKeys();
                return rs.next() ? rs.getObject(1) : null;
            } catch (SQLException e) {
                log.error("修改出错,sql:" + sql, e);
                throw new RuntimeException(e);
            } finally {
                try {
                    if (rs != null) {
                        rs.close();
                        rs = null;
                    }
                    if (ps != null) {
                        ps.close();
                        ps = null;
                    }
                    if (connection != null && connection.getAutoCommit()) {
                        closeCon(connection);
                    }
                } catch (SQLException e) {
                    log.error(e.getMessage(), e);
                }
            }
        } else {
            LogUtil.daoDebug("canSave=false不执行保存 sql: " + sql + " params: " + Arrays.asList(params));
            return 0;
        }
    }

    /**
     * 
     * 保存数据并返回id,注意被操作的数据表主键id必须设置AUTO_INCREMENT属性。数据库自增的id和手动插入的id均可正常返回。
     * 
     * @Title saveAndReturnId
     * @param modelBean
     *            实体bean
     * @return Object
     */
    public <T> Object saveAndReturnId(T modelBean) {
        if (modelBean != null) {
            SqlModel sqlModel = getInsertSqlModel(modelBean, false);
            return updateAndReturnId(sqlModel.getSql(), sqlModel.getParams());
        }
        return null;
    }

    /**
     * 
     * 根据查询条件返回实体bean
     * 
     * @Title getOne
     * @param queryModel
     *            查询条件
     * @return T
     */
    @Override
    public <T> T getOne(Class<T> classOfT, QueryModel queryModel) {
        String tableName = getTableName(classOfT);
        String sql = "SELECT " + queryModel.getSelectFields() + " FROM " + tableName + " " + queryModel.getOrderQueryStr() + " limit 1";
        return getOne(classOfT, sql, queryModel.getParams());
    }

    /**
     * 
     * 根据查询条件返回具体条数的list
     * 
     * @Title getList
     * @param queryModel
     *            查询条件
     * @param size
     *            条数
     * @return List<T>
     */
    @Override
    public <T> List<T> getList(Class<T> classOfT, QueryModel queryModel, int size) {
        String tableName = getTableName(classOfT);
        String sql = "SELECT " + queryModel.getSelectFields() + " FROM " + tableName + " " + queryModel.getOrderQueryStr() + " LIMIT "
                + size;
        return getList(classOfT, sql, queryModel.getParams());
    }

    /**
     * 
     * 根据查询sql返回 list数据
     * 
     * @Title getListBySql
     * @param queryModel
     *            查询条件
     * @param condition
     *            SQL条件
     * @param size
     *            查询条数
     * @return List<T>
     */
    public <T> List<T> getListBySql(Class<T> classOfT, QueryModel queryModel, String condition, int size) {
        String tableName = getTableName(classOfT);
        String sql = "SELECT " + queryModel.getSelectFields() + " FROM " + tableName + " WHERE " + condition + " LIMIT " + size;
        return getList(classOfT, sql, queryModel.getParams());
    }

    @Override
    public <T> List<T> getListBySql(Class<T> classOfT, String sql, Object... params) {
        return getList(classOfT, sql, params);
    }

    @Override
    public Map<String, Object> getMapSql(String sql, Object... params) {
        return getMap(sql, params);
    }

    /**
     * 
     * 根据查询条件返回List<Map>
     * 
     * @Title getMap
     * @param sql
     *            sql语句
     * @param sql
     *            查询条件
     * @return Map<String,Object>
     */
    @Override
    public List<Map<String, Object>> getListMapSql(String sql, Object... params) {
        return getMapList(sql, params);
    }

    /**
     * 
     * 返回分页数据
     * 
     * @Title getPageNavigationBean
     * @param queryModel
     *            查询条件
     * @param pageNum
     *            页码
     * @param pageSize
     *            条数
     * @return PageNavigationBean<T>
     */
    @Override
    public <T> PageNavigationBean<T> getPageNavigationBean(Class<T> classOfT, QueryModel queryModel, int pageNum, int pageSize) {
        PageNavigationBean<T> pageBean = new PageNavigationBean<>();
        // 处理pageNum传值小于1时，分页语句报错；
        pageNum = pageNum < 1 ? 1 : pageNum;
        pageBean.setCurrentPage(pageNum);
        pageBean.setPageSize(pageSize);
        String tableName = getTableName(classOfT);
        String countSql = "SELECT COUNT(1) FROM " + tableName + " " + queryModel.getNoOrderQueryStr();

        String pageBeanSql = "SELECT " + queryModel.getSelectFields() + " FROM " + tableName + " " + queryModel.getOrderQueryStr();

        // 如果带着group by则单独处理 TODO注意此处很有可能随着升级而不兼容，注意调整
        if (com.ld.shieldsb.common.core.util.StringUtils.isNotEmpty(queryModel.getOrderQueryStr())
                && queryModel.getOrderQueryStr().toLowerCase().contains(" group by")) {
            countSql = "SELECT COUNT(1) FROM (" + pageBeanSql + ") tmp ";
        }

        int count = getCount(countSql, queryModel.getParams());

        pageBean.setTotalCount(count);

        int startRowNum = pageBean.getCurrentPoint() - 1;

        pageBeanSql += " LIMIT " + startRowNum + "," + pageSize;

        List<T> list = getList(classOfT, pageBeanSql, queryModel.getParams());
        pageBean.setResultList(list);
        return pageBean;
    }

    /**
     * 自定义查询语句的分页（select语句，不带分页信息）
     * 
     * @Title getPageNavigationBean
     * @author 吕凯
     * @date 2019年8月15日 上午8:11:22
     * @param classOfT
     * @param sql
     *            select查询语句不带分页
     * @param pageNum
     * @param pageSize
     * @param params
     * @return PageNavigationBean<T>
     */
    public <T> PageNavigationBean<T> getPageNavigationBean(Class<T> classOfT, String sql, int pageNum, int pageSize, Object... params) {
        sql = sql.trim(); // 去除首尾空格
        PageNavigationBean<T> pageBean = new PageNavigationBean<>();
        // 处理pageNum传值小于1时，分页语句报错；
        pageNum = pageNum < 1 ? 1 : pageNum;
        pageBean.setCurrentPage(pageNum);
        pageBean.setPageSize(pageSize);

        Matcher m = CONDITION_PATTERN.matcher(sql);
        String countSql = sql;
        if (m.find()) {
            String valuesStr = m.group(0); // 代表整个符合条件的字符串
            if (countSql.indexOf(valuesStr) == 0) { // 替换开头的select ***
                countSql = "SELECT COUNT(1) FROM " + countSql.substring(valuesStr.length());
            }
        }
        int count = getCount(countSql, params);

        pageBean.setTotalCount(count);

        int startRowNum = pageBean.getCurrentPoint() - 1;

        List<T> list = getList(classOfT, sql + " LIMIT " + startRowNum + "," + pageSize, params);
        pageBean.setResultList(list);
        return pageBean;
    }

    /**
     * 自定义count和select sql语句的分页查询
     * 
     * @Title getPageNavigationBean
     * @author 吕凯
     * @date 2019年8月15日 上午8:10:06
     * @param classOfT
     * @param countSql
     *            count语句
     * @param selectSql
     *            select语句不带分页
     * @param pageNum
     *            页数
     * @param pageSize
     *            每页条数
     * @param params
     * @return PageNavigationBean<T>
     */
    public <T> PageNavigationBean<T> getPageNavigationBean(Class<T> classOfT, String countSql, String selectSql, int pageNum, int pageSize,
            Object... params) {
        PageNavigationBean<T> pageBean = new PageNavigationBean<>();
        // 处理pageNum传值小于1时，分页语句报错；
        pageNum = pageNum < 1 ? 1 : pageNum;
        pageBean.setCurrentPage(pageNum);
        pageBean.setPageSize(pageSize);
        int count = getCount(countSql, params);

        pageBean.setTotalCount(count);

        int startRowNum = pageBean.getCurrentPoint() - 1;

        List<T> list = getList(classOfT, selectSql + " LIMIT " + startRowNum + "," + pageSize, params);
        pageBean.setResultList(list);
        return pageBean;
    }

    @Override
    public <T> PageNavigationBean<Map<String, Object>> getPageNavigationMap(Class<T> classOfT, QueryModel queryModel, int pageNum,
            int pageSize) {
        PageNavigationBean<Map<String, Object>> pageBean = new PageNavigationBean<>();
        // 处理pageNum传值小于1时，分页语句报错；
        pageNum = pageNum < 1 ? 1 : pageNum;
        pageBean.setCurrentPage(pageNum);
        pageBean.setPageSize(pageSize);
        String tableName = getTableName(classOfT);
        String countSql = "SELECT COUNT(1) FROM " + tableName + " " + queryModel.getNoOrderQueryStr();

        String pageBeanSql = "SELECT " + queryModel.getSelectFields() + " FROM " + tableName + " " + queryModel.getOrderQueryStr();

        // 如果带着group by则单独处理 TODO注意此处很有可能随着升级而不兼容，注意调整
        if (com.ld.shieldsb.common.core.util.StringUtils.isNotEmpty(queryModel.getOrderQueryStr())
                && queryModel.getOrderQueryStr().toLowerCase().contains(" group by")) {
            countSql = "SELECT COUNT(1) FROM (" + pageBeanSql + ") tmp ";
        }

        int count = getCount(countSql, queryModel.getParams());

        pageBean.setTotalCount(count);

        int startRowNum = pageBean.getCurrentPoint() - 1;
        pageBeanSql += " LIMIT " + startRowNum + "," + pageSize;

        List<Map<String, Object>> list = getMapList(pageBeanSql, queryModel.getParams());
        pageBean.setResultList(list);
        return pageBean;
    }

    /**
     * 初始化joinModel
     * 
     * @Title initJoin
     * @author 吕凯
     * @date 2019年8月15日 下午2:37:40
     * @param classOfT
     * @return JoinModel
     */
    @Override
    public <T> MySqlBaseDao initJoin(Class<T> classOfT, QueryModel queryModel) {
        JoinModel model = new JoinModel(classOfT, queryModel);
        setJoinModel(model);
        return this;
    }

    /**
     * jion操作，只join一级关联的表，如一对一，多对多的对象和中间表
     * 
     * @Title join
     * @author 吕凯
     * @date 2019年8月16日 下午4:49:52
     * @param property
     * @param queryModel
     * @return
     * @throws Exception
     *             MySqlBaseDao
     */
    @Override
    public <T> MySqlBaseDao join(Property<T, ?> property, QueryModel queryModel) throws Exception {
        return join(property, queryModel, null);
    }

    /**
     * 用于join多对多
     * 
     * @Title join
     * @author 吕凯
     * @date 2019年8月16日 下午4:51:14
     * @param property
     * @param middleQueryModel
     * @param queryModel
     * @return
     * @throws Exception
     *             MySqlBaseDao
     */
    @Override
    public <T> MySqlBaseDao join(Property<T, ?> property, QueryModel middleQueryModel, QueryModel otherQueryModel) throws Exception {
        JoinModel joinModel = getJoinModel();
        if (joinModel == null) {
            throw new Exception("未调用initJoin初始化！");
        }
        String fieldName = FunctionUtil.getFieldName(property);
        Field field = AnnotationUtil.getField(joinModel.getMainClass(), fieldName);
        if (otherQueryModel == null) {
            joinModel.addLinkedTable(field, middleQueryModel);
        } else {
            joinModel.addLinkedTable(field, middleQueryModel, otherQueryModel); // 添加
        }
        return this;
    }

    @SuppressWarnings("unchecked")
    public <T> T getOneByJoin() throws Exception {
        JoinModel joinModel = getJoinModel();
        if (joinModel == null) {
            throw new Exception("未调用initJoin初始化！");
        }
        joinModel.getSqlAndParams();
        T obj = (T) getOne(joinModel.getMainClass(), joinModel.getSql(), joinModel.getParams());
        // clearJoin(); // 获取到结果后就清除,手动关闭
        return obj;
    }

    @SuppressWarnings("unchecked")
    @Override
    public <T> List<T> getListByJoin() throws Exception {
        JoinModel joinModel = getJoinModel();
        if (joinModel == null) {
            throw new Exception("未调用initJoin初始化！");
        }
        joinModel.getSqlAndParams();
        List<T> list = (List<T>) getList(joinModel.getMainClass(), joinModel.getSql(), joinModel.getParams());
        // clearJoin(); // 获取到结果后就清除,手动关闭
        return list;
    }

    @SuppressWarnings("unchecked")
    @Override
    public <T> PageNavigationBean<T> getPageNavigationBeanByJoin(int pageNum, int pageSize) throws Exception {
        JoinModel joinModel = getJoinModel();
        if (joinModel == null) {
            throw new Exception("未调用initJoin初始化！");
        }
        joinModel.getSqlAndParams();
        PageNavigationBean<T> pageBean = (PageNavigationBean<T>) getPageNavigationBean(joinModel.getMainClass(), joinModel.getSql(),
                pageNum, pageSize, joinModel.getParams());
        // clearJoin(); // 获取到结果后就清除,手动关闭
        return pageBean;
    }

    /**
     * 根据QueryModel生成sql语句
     * 
     * @Title getSql
     * @author 吕凯
     * @date 2021年1月6日 下午2:26:33
     * @param <T>
     * @param classOfT
     * @param queryModel
     * @return String
     */
    public <T> String getSql(Class<T> classOfT, QueryModel queryModel) {
        return getSql(classOfT, queryModel, null, null);
    }

    /**
     * 根据QueryModel生成sql语句
     * 
     * @Title getSql
     * @author 吕凯
     * @date 2019年8月15日 上午11:57:16
     * @param classOfT
     * @param queryModel
     * @param pageNum
     * @param pageSize
     * @return String
     */
    public <T> String getSql(Class<T> classOfT, QueryModel queryModel, Integer pageNum, Integer pageSize) {
        String tableName = getTableName(classOfT);
        // 处理pageNum传值小于1时，分页语句报错；
        if (pageNum != null) {
            pageNum = pageNum < 1 ? Integer.valueOf(1) : pageNum;
        }
        StringBuffer sb = new StringBuffer();
        sb.append("select ").append(queryModel.getSelectFields()).append(" FROM ").append(tableName).append(" ")
                .append(queryModel.getOrderQueryStr());

        if (pageNum != null && pageSize != null) {
            int startRowNum = pageNum - 1;
            sb.append(" limit ").append(startRowNum).append(",").append(pageSize);
        }

        return sb.toString();
    }

    public <T> List<T> getTopList(Class<T> classOfT, String sql, int rownum) {
        String pageSql = "SELECT * FROM (" + sql + ") WHERE LIMIT<= ?";
        return super.getList(classOfT, pageSql, rownum);
    }

}
